package db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import de.dnb.basics.applicationComponents.DbProperties;
import de.dnb.gnd.parser.Record;
import de.dnb.gnd.parser.line.Line;
import de.dnb.gnd.utils.DownloadWorker;
import de.dnb.gnd.utils.GNDUtils;
import de.dnb.gnd.utils.RecordUtils;
import de.dnb.gnd.utils.SubfieldUtils;

public class FillDB extends DownloadWorker {

	Connection con = null;

	PreparedStatement preparedStatementSW;

	PreparedStatement preparedStatementOB;

	int i = 0;

	@Override
	protected void processRecord(Record record) {

		if (RecordUtils.isNullOrEmpty(record)) {
			System.err.println("LEER");
			return;
		}

		if (!GNDUtils.isValid(record))
			return;

		i++;

		String name = "";
		// System.err.println(i);
		int level = GNDUtils.getLevel(record);
		String type = Character.toString(GNDUtils.getRecordType(record));
		switch (type) {
		case "s":
			if (level > 3)
				return;
			break;
		case "g":
			if (level > 3)
				return;
			break;
		case "b":
			if (level > 1)
				return;
			break;

		default:
			break;
		}

		try {

			String idnRec = record.getId();
			name = GNDUtils.getNameOfRecord(record);
			if (i % 10000 == 0)
				System.err.println(name);

			preparedStatementSW.setString(1, idnRec);
			preparedStatementSW.setString(2, type);
			preparedStatementSW.setInt(3, level);
			preparedStatementSW.setString(4, name);
			preparedStatementSW.executeUpdate();

			List<Line> obb = GNDUtils.getOBB(record);

			for (Line line : obb) {
				String idnOB = SubfieldUtils.getContentOfFirstSubfield(line,
						'9');
				if (idnOB != null) {
					String code = SubfieldUtils.getContentOfFirstSubfield(line,
							'4');

					preparedStatementOB.setString(1, idnRec);
					preparedStatementOB.setString(2, idnOB);
					preparedStatementOB.setString(3, code);
					preparedStatementOB.executeUpdate();
				}
			}

		} catch (Exception e) {
			System.err.println(record);
			System.err.println(e);
		}
	}

	private void clearTables() throws SQLException {
		System.err.println("Lösche alte Tabellen ...");
		Statement stmt = con.createStatement();
		System.err.println("Lösche sw ...");
		stmt.executeUpdate("DELETE FROM sw");
		System.err.println("Lösche rel ...");
		stmt.executeUpdate("DELETE FROM rel");
		stmt.close();
	}

	public static void main(String[] args) throws IOException {

		FillDB fillDB = new FillDB();

		try {

			DbProperties properties = new DbProperties(
					"./dbconfig/db.properties");
			fillDB.con = DriverManager.getConnection(properties.getUrl(),
					properties.getUserName(), properties.getPassword());

			fillDB.clearTables();

			fillDB.preparedStatementSW = fillDB.con
					.prepareStatement("INSERT INTO sw (id, type, level, name) "
							+ "VALUES (?,?,?,?)");
			fillDB.preparedStatementOB = fillDB.con
					.prepareStatement("INSERT INTO rel (ub, ob, code) "
							+ "VALUES (?,?,?)");

			System.err.println("Fülle Datenbank ...");
			fillDB.gzipSettings();
			fillDB.setFolder("D:/db");
			fillDB.setFilePrefix("DNBGND_");
			fillDB.processAllFiles();

			fillDB.preparedStatementSW.close();
			fillDB.preparedStatementOB.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (fillDB.con != null)
				try {
					fillDB.con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

}